package com.crm.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.crm.entity.User;

public interface UserRepository extends JpaRepository<User, String>, JpaSpecificationExecutor<User> {
	 /**
     * 修改锁定时间
     * @param loginName
     * @return
     */
	@Transactional
    @Modifying
    @Query(value = "update users set user_lockout_time =now() where   user_name=?1  ",nativeQuery = true)
    Integer updateTime(String  loginName);
	/**
     * 查询被锁定的用户ID
     * @param loginName
     * @return
     */
    @Query(value = "select user_id from users  where   user_name=?1  and  user_is_lockout = 1  ",nativeQuery = true)
    String Updatewrotime(String  loginName);
	/**
     * 修改用户封号和解封的状态
     * @param lockout
     * @param id
     * @return
     */
    @Transactional
    @Query(value = "update  users set  user_is_lockout = ?1,user_pass_wrong_cout = ?2 where user_id=?3 " ,nativeQuery = true)
    @Modifying
    Integer updateIsLockOut(String lockout,String  cishu,String id);
	/**
     * 定时器解封
     * @param loginName
     * @return
     */
    @Query(value = " select  user_id  from users  where  user_name=?1 and now() > date_sub(user_lockout_time,interval -1 day)" ,nativeQuery = true)
    String AutoJieFeng(String loginName);
    @Query(value = "select user_pass_wrong_cout from users where user_name=?1",nativeQuery = true)
    Integer AutoJiefeng2(String loginName);
	/**
     * 查询所有密码错误五次以上的用户
     * @return
     */
    @Query(value = "select user_id from users where  user_pass_wrong_cout >=5 " ,nativeQuery = true)
    List<String> YNGt5();
    /**
     * 查询密码错误5次以上的用户ID
     * @param loginName
     * @return
     */
    @Query(value ="select user_id from users  where   user_name=?1  and  user_pass_wrong_cout >5 " ,nativeQuery = true)
    String Updatelock(String  loginName);

    
    /**
	 * 每次登录密码错误 +一次登录失败信息
	 * 
	 * @param loginName
	 */
	@Modifying
	@Query(value = "update users set user_pass_wrong_cout= user_pass_wrong_cout+1  where user_name = ?1", nativeQuery = true)
	void updateIsLockOut(String loginName);

	/**
	 * 通过ID查询用户名
	 * 
	 * @param id
	 * @return
	 */
	@Query(value = " select  user_name  from users  where  user_id=?1 ", nativeQuery = true)
	String findLoginName(String id);

	/**
	 * 修改默认登录的时间
	 * 
	 * @param loginName
	 * @return
	 */
	@Transactional
	@Modifying
	@Query(value = "update users set user_last_login_time =now() where   user_name=?1  ", nativeQuery = true)
	Integer lastLoginTime(String loginName);

	/**
	 * 通过登录名查询用户ID
	 * 
	 * @param loginName
	 * @return
	 */
	@Query(value = " select  user_id  from users  where  user_name=?1 ", nativeQuery = true)
	String findId(String userName);

	// 自定义方法 根据用户名查找数据
	User findByUserName(String userName);

	/**
	 * 根据所输入的信息判断是否输入的是否符合条件
	 */
	@Query(value = "SELECT * from users u WHERE u.user_name = ?1 and u.user_telephone=?2", nativeQuery = true)
	User finBynameandPhone(String uname, String phone);

	/**
	 * 更改为输入的新密码
	 */
	@Query(value = "UPDATE `crm`.`users` SET `user_pass_word` =?2  WHERE `user_name`=?1", nativeQuery = true)
	@Transactional
	@Modifying
	Integer updPwdByNewPwd(String uname, String npassword);
	
	
	@Query(value="UPDATE  `crm`.`users` SET   user_is_lockout=0,  user_pass_wrong_cout=0,user_lockout_time=?2  WHERE `user_id`=?1 ", nativeQuery=true)
	@Transactional
	@Modifying
	Integer jieFeng(String id,String time);
	
	@Query(value="UPDATE  `crm`.`users` SET     user_pass_wrong_cout=5,user_is_lockout=1, user_lockout_time=?2 WHERE `user_id`=?1 ", nativeQuery=true)
	@Transactional
	@Modifying
	Integer suoDing(String id,String time);
	
	
	/**
	 * 删除用户角色关系
	 * @param id
	 * @return
	 */
	@Query(value="delete  from user_role  where user_id=?1 ", nativeQuery=true)
	@Transactional
	@Modifying
	Integer  deleteUser_Role(String id);
    
	/**
	 * 添加用户角色关系
	 * @param id
	 * @param roleId
	 * @return
	 */
	@Query(value="INSERT INTO `crm`.`user_role` (`user_id`, `role_id`) VALUES (?1, ?2)", nativeQuery=true)
	@Transactional
	@Modifying
	Integer  addUser_Role(String id,Integer rid);
	
	/**
	 * 删除用户和咨询师的关系
	 * @param id
	 * @return
	 */
	@Query(value="delete from askers where user_id=?1", nativeQuery=true)
	@Transactional
	@Modifying	
	Integer deleteUser_Asker(String id);
	/**
	 * 删除用户和网络咨询师的关系
	 * @param id
	 * @return
	 */
	@Query(value="delete from netakers where user_id=?1", nativeQuery=true)
	@Transactional
	@Modifying	
	Integer deleteUser_NetAsker(String id);
	/**
	 * 添加用户网络咨询师关系
	 * @param id
	 * @return
	 */
	@Query(value="INSERT INTO `crm`.`netakers` ( `user_id`) VALUES (?1);", nativeQuery=true)
	@Transactional
	@Modifying	
	Integer addUser_NetAsker(String id);
	
	/**
	 * 添加用户咨询师关系
	 * @param id
	 * @return
	 */
	@Query(value="INSERT INTO `crm`.`askers` ( `user_id`) VALUES (?1);", nativeQuery=true)
	@Transactional
	@Modifying	
	Integer addUser_Asker(String id);
	
	
	
	/**
	 * 修改签到状态
	 * @param id
	 * @param checkState
	 * @return
	 */
	@Query(value="UPDATE `crm`.`users` SET   `check_state`=?2  WHERE (`user_id`=?1);", nativeQuery=true)
	@Transactional
	@Modifying	
	Integer updateCheckState(String id,String checkState);
	
	
	/**
	 * 重置签到状态
	 * @return
	 */
	@Query(value="UPDATE `crm`.`users` SET   `check_state`='未签到'  ", nativeQuery=true)
	@Transactional
	@Modifying	
	Integer reSetCheckState();
	
	
	/**
	 * 通过ID查询用户name
	 *
	 * @return
	 */
	@Query(value = " select  *  from users  where  user_id=?1 ", nativeQuery = true)
	User findWebSocketUname(String id);
	/**
	 * 查询已经签到的人数
	 * @return
	 */
	@Query(value = " select count(*) from users where check_state not in ('未签到');", nativeQuery = true)
    Integer countCheckIn();
	
	/**
	 * 查询未签到的人数
	 * @return
	 */
	@Query(value = " select count(*) from users where check_state='未签到'", nativeQuery = true)
    Integer countCheckOut();
}
